-- 账变记录表
CREATE TABLE account_changes (
    id SERIAL NOT NULL,
    bill_no CHAR(20) NOT NULL DEFAULT '',               -- 账变单号
    date DATE NOT NULL DEFAULT CURRENT_DATE,            -- 日期
    user_id INT NOT NULL,                               -- 用户编号
    user_name VARCHAR(20) NOT NULL DEFAULT '',          -- 用户名称
    amount DECIMAL(16, 4) NOT NULL DEFAULT 0,           -- 账变金额
    type  SMALLINT NOT NULL DEFAULT 0,                  -- 类型, 1: 充值; 2: 提现; 21: 提现拒绝;
    in_out SMALLINT NOT NULL DEFAULT 0,                 -- IO类型, 1: 入账; 2: 出账; 31: 平账冻结; 32: 平账回退
    before_remain DECIMAL(16, 4) NOT NULL DEFAULT 0,    -- 变前可用余额
    before_frozen DECIMAL(16, 4) NOT NULL DEFAULT 0,    -- 变前冻结金额
    before_total DECIMAL(16, 4) NOT NULL DEFAULT 0,     -- 变前总额
    after_remain DECIMAL(16, 4) NOT NULL DEFAULT 0,     -- 变后余额
    after_frozen DECIMAL(16, 4) NOT NULL DEFAULT 0,     -- 变后冻结金额
    after_total DECIMAL(16, 4) NOT NULL DEFAULT 0,      -- 变后部愘同
    remark VARCHAR(200) NOT NULL DEFAULT '',            -- 账变备注
    created TIMESTAMPTZ NOT NULL DEFAULT NOW(),         -- 账变时间
    FOREIGN KEY (user_id) REFERENCES users(id),
    PRIMARY KEY(id, date)
) PARTITION BY RANGE (date);

-- 索引
CREATE INDEX ON account_changes(date);
CREATE INDEX ON account_changes(created);

-- 分区/按半月
CREATE TABLE account_changes_202005_01 PARTITION OF account_changes FOR VALUES FROM ('2020-05-01') TO ('2020-05-06');
CREATE TABLE account_changes_202005_02 PARTITION OF account_changes FOR VALUES FROM ('2020-05-06') TO ('2020-05-11');
CREATE TABLE account_changes_202005_03 PARTITION OF account_changes FOR VALUES FROM ('2020-05-11') TO ('2020-05-16');
CREATE TABLE account_changes_202005_04 PARTITION OF account_changes FOR VALUES FROM ('2020-05-16') TO ('2020-05-21');
CREATE TABLE account_changes_202005_05 PARTITION OF account_changes FOR VALUES FROM ('2020-05-21') TO ('2020-05-26');
CREATE TABLE account_changes_202005_06 PARTITION OF account_changes FOR VALUES FROM ('2020-05-26') TO ('2020-06-01');
CREATE TABLE account_changes_202006_01 PARTITION OF account_changes FOR VALUES FROM ('2020-06-01') TO ('2020-06-06');
CREATE TABLE account_changes_202006_02 PARTITION OF account_changes FOR VALUES FROM ('2020-06-06') TO ('2020-06-11');
CREATE TABLE account_changes_202006_03 PARTITION OF account_changes FOR VALUES FROM ('2020-06-11') TO ('2020-06-16');
CREATE TABLE account_changes_202006_04 PARTITION OF account_changes FOR VALUES FROM ('2020-06-16') TO ('2020-06-21');
CREATE TABLE account_changes_202006_05 PARTITION OF account_changes FOR VALUES FROM ('2020-06-21') TO ('2020-06-26');
CREATE TABLE account_changes_202006_06 PARTITION OF account_changes FOR VALUES FROM ('2020-06-26') TO ('2020-07-01');
CREATE TABLE account_changes_202007_01 PARTITION OF account_changes FOR VALUES FROM ('2020-07-01') TO ('2020-07-06');
CREATE TABLE account_changes_202007_02 PARTITION OF account_changes FOR VALUES FROM ('2020-07-06') TO ('2020-07-11');
CREATE TABLE account_changes_202007_03 PARTITION OF account_changes FOR VALUES FROM ('2020-07-11') TO ('2020-07-16');
CREATE TABLE account_changes_202007_04 PARTITION OF account_changes FOR VALUES FROM ('2020-07-16') TO ('2020-07-21');
CREATE TABLE account_changes_202007_05 PARTITION OF account_changes FOR VALUES FROM ('2020-07-21') TO ('2020-07-26');
CREATE TABLE account_changes_202007_06 PARTITION OF account_changes FOR VALUES FROM ('2020-07-26') TO ('2020-08-01');
CREATE TABLE account_changes_202008_01 PARTITION OF account_changes FOR VALUES FROM ('2020-08-01') TO ('2020-08-06');
CREATE TABLE account_changes_202008_02 PARTITION OF account_changes FOR VALUES FROM ('2020-08-06') TO ('2020-08-11');
CREATE TABLE account_changes_202008_03 PARTITION OF account_changes FOR VALUES FROM ('2020-08-11') TO ('2020-08-16');
CREATE TABLE account_changes_202008_04 PARTITION OF account_changes FOR VALUES FROM ('2020-08-16') TO ('2020-08-21');
CREATE TABLE account_changes_202008_05 PARTITION OF account_changes FOR VALUES FROM ('2020-08-21') TO ('2020-08-26');
CREATE TABLE account_changes_202008_06 PARTITION OF account_changes FOR VALUES FROM ('2020-08-26') TO ('2020-09-01');
CREATE TABLE account_changes_202009_01 PARTITION OF account_changes FOR VALUES FROM ('2020-09-01') TO ('2020-09-06');
CREATE TABLE account_changes_202009_02 PARTITION OF account_changes FOR VALUES FROM ('2020-09-06') TO ('2020-09-11');
CREATE TABLE account_changes_202009_03 PARTITION OF account_changes FOR VALUES FROM ('2020-09-11') TO ('2020-09-16');
CREATE TABLE account_changes_202009_04 PARTITION OF account_changes FOR VALUES FROM ('2020-09-16') TO ('2020-09-21');
CREATE TABLE account_changes_202009_05 PARTITION OF account_changes FOR VALUES FROM ('2020-09-21') TO ('2020-09-26');
CREATE TABLE account_changes_202009_06 PARTITION OF account_changes FOR VALUES FROM ('2020-09-26') TO ('2020-10-01');
CREATE TABLE account_changes_202010_01 PARTITION OF account_changes FOR VALUES FROM ('2020-10-01') TO ('2020-10-06');
CREATE TABLE account_changes_202010_02 PARTITION OF account_changes FOR VALUES FROM ('2020-10-06') TO ('2020-10-11');
CREATE TABLE account_changes_202010_03 PARTITION OF account_changes FOR VALUES FROM ('2020-10-11') TO ('2020-10-16');
CREATE TABLE account_changes_202010_04 PARTITION OF account_changes FOR VALUES FROM ('2020-10-16') TO ('2020-10-21');
CREATE TABLE account_changes_202010_05 PARTITION OF account_changes FOR VALUES FROM ('2020-10-21') TO ('2020-10-26');
CREATE TABLE account_changes_202010_06 PARTITION OF account_changes FOR VALUES FROM ('2020-10-26') TO ('2020-11-01');
CREATE TABLE account_changes_202011_01 PARTITION OF account_changes FOR VALUES FROM ('2020-11-01') TO ('2020-11-06');
CREATE TABLE account_changes_202011_02 PARTITION OF account_changes FOR VALUES FROM ('2020-11-06') TO ('2020-11-11');
CREATE TABLE account_changes_202011_03 PARTITION OF account_changes FOR VALUES FROM ('2020-11-11') TO ('2020-11-16');
CREATE TABLE account_changes_202011_04 PARTITION OF account_changes FOR VALUES FROM ('2020-11-16') TO ('2020-11-21');
CREATE TABLE account_changes_202011_05 PARTITION OF account_changes FOR VALUES FROM ('2020-11-21') TO ('2020-11-26');
CREATE TABLE account_changes_202011_06 PARTITION OF account_changes FOR VALUES FROM ('2020-11-26') TO ('2020-12-01');
CREATE TABLE account_changes_202012_01 PARTITION OF account_changes FOR VALUES FROM ('2020-12-01') TO ('2020-12-06');
CREATE TABLE account_changes_202012_02 PARTITION OF account_changes FOR VALUES FROM ('2020-12-06') TO ('2020-12-11');
CREATE TABLE account_changes_202012_03 PARTITION OF account_changes FOR VALUES FROM ('2020-12-11') TO ('2020-12-16');
CREATE TABLE account_changes_202012_04 PARTITION OF account_changes FOR VALUES FROM ('2020-12-16') TO ('2020-12-21');
CREATE TABLE account_changes_202012_05 PARTITION OF account_changes FOR VALUES FROM ('2020-12-21') TO ('2020-12-26');
CREATE TABLE account_changes_202012_06 PARTITION OF account_changes FOR VALUES FROM ('2020-12-26') TO ('2021-01-01');
CREATE TABLE account_changes_202101_01 PARTITION OF account_changes FOR VALUES FROM ('2021-01-01') TO ('2021-01-06');
CREATE TABLE account_changes_202101_02 PARTITION OF account_changes FOR VALUES FROM ('2021-01-06') TO ('2021-01-11');
CREATE TABLE account_changes_202101_03 PARTITION OF account_changes FOR VALUES FROM ('2021-01-11') TO ('2021-01-16');
CREATE TABLE account_changes_202101_04 PARTITION OF account_changes FOR VALUES FROM ('2021-01-16') TO ('2021-01-21');
CREATE TABLE account_changes_202101_05 PARTITION OF account_changes FOR VALUES FROM ('2021-01-21') TO ('2021-01-26');
CREATE TABLE account_changes_202101_06 PARTITION OF account_changes FOR VALUES FROM ('2021-01-26') TO ('2021-02-01');
CREATE TABLE account_changes_202102_01 PARTITION OF account_changes FOR VALUES FROM ('2021-02-01') TO ('2021-02-06');
CREATE TABLE account_changes_202102_02 PARTITION OF account_changes FOR VALUES FROM ('2021-02-06') TO ('2021-02-11');
CREATE TABLE account_changes_202102_03 PARTITION OF account_changes FOR VALUES FROM ('2021-02-11') TO ('2021-02-16');
CREATE TABLE account_changes_202102_04 PARTITION OF account_changes FOR VALUES FROM ('2021-02-16') TO ('2021-02-21');
CREATE TABLE account_changes_202102_05 PARTITION OF account_changes FOR VALUES FROM ('2021-02-21') TO ('2021-02-26');
CREATE TABLE account_changes_202102_06 PARTITION OF account_changes FOR VALUES FROM ('2021-02-26') TO ('2021-03-01');
CREATE TABLE account_changes_202103_01 PARTITION OF account_changes FOR VALUES FROM ('2021-03-01') TO ('2021-03-06');
CREATE TABLE account_changes_202103_02 PARTITION OF account_changes FOR VALUES FROM ('2021-03-06') TO ('2021-03-11');
CREATE TABLE account_changes_202103_03 PARTITION OF account_changes FOR VALUES FROM ('2021-03-11') TO ('2021-03-16');
CREATE TABLE account_changes_202103_04 PARTITION OF account_changes FOR VALUES FROM ('2021-03-16') TO ('2021-03-21');
CREATE TABLE account_changes_202103_05 PARTITION OF account_changes FOR VALUES FROM ('2021-03-21') TO ('2021-03-26');
CREATE TABLE account_changes_202103_06 PARTITION OF account_changes FOR VALUES FROM ('2021-03-26') TO ('2021-04-01');
CREATE TABLE account_changes_202104_01 PARTITION OF account_changes FOR VALUES FROM ('2021-04-01') TO ('2021-04-06');
CREATE TABLE account_changes_202104_02 PARTITION OF account_changes FOR VALUES FROM ('2021-04-06') TO ('2021-04-11');
CREATE TABLE account_changes_202104_03 PARTITION OF account_changes FOR VALUES FROM ('2021-04-11') TO ('2021-04-16');
CREATE TABLE account_changes_202104_04 PARTITION OF account_changes FOR VALUES FROM ('2021-04-16') TO ('2021-04-21');
CREATE TABLE account_changes_202104_05 PARTITION OF account_changes FOR VALUES FROM ('2021-04-21') TO ('2021-04-26');
CREATE TABLE account_changes_202104_06 PARTITION OF account_changes FOR VALUES FROM ('2021-04-26') TO ('2021-05-01');
CREATE TABLE account_changes_202105_01 PARTITION OF account_changes FOR VALUES FROM ('2021-05-01') TO ('2021-05-06');
CREATE TABLE account_changes_202105_02 PARTITION OF account_changes FOR VALUES FROM ('2021-05-06') TO ('2021-05-11');
CREATE TABLE account_changes_202105_03 PARTITION OF account_changes FOR VALUES FROM ('2021-05-11') TO ('2021-05-16');
CREATE TABLE account_changes_202105_04 PARTITION OF account_changes FOR VALUES FROM ('2021-05-16') TO ('2021-05-21');
CREATE TABLE account_changes_202105_05 PARTITION OF account_changes FOR VALUES FROM ('2021-05-21') TO ('2021-05-26');
CREATE TABLE account_changes_202105_06 PARTITION OF account_changes FOR VALUES FROM ('2021-05-26') TO ('2021-06-01');
CREATE TABLE account_changes_202106_01 PARTITION OF account_changes FOR VALUES FROM ('2021-06-01') TO ('2021-06-06');
CREATE TABLE account_changes_202106_02 PARTITION OF account_changes FOR VALUES FROM ('2021-06-06') TO ('2021-06-11');
CREATE TABLE account_changes_202106_03 PARTITION OF account_changes FOR VALUES FROM ('2021-06-11') TO ('2021-06-16');
CREATE TABLE account_changes_202106_04 PARTITION OF account_changes FOR VALUES FROM ('2021-06-16') TO ('2021-06-21');
CREATE TABLE account_changes_202106_05 PARTITION OF account_changes FOR VALUES FROM ('2021-06-21') TO ('2021-06-26');
CREATE TABLE account_changes_202106_06 PARTITION OF account_changes FOR VALUES FROM ('2021-06-26') TO ('2021-07-01');
CREATE TABLE account_changes_202107_01 PARTITION OF account_changes FOR VALUES FROM ('2021-07-01') TO ('2021-07-06');
CREATE TABLE account_changes_202107_02 PARTITION OF account_changes FOR VALUES FROM ('2021-07-06') TO ('2021-07-11');
CREATE TABLE account_changes_202107_03 PARTITION OF account_changes FOR VALUES FROM ('2021-07-11') TO ('2021-07-16');
CREATE TABLE account_changes_202107_04 PARTITION OF account_changes FOR VALUES FROM ('2021-07-16') TO ('2021-07-21');
CREATE TABLE account_changes_202107_05 PARTITION OF account_changes FOR VALUES FROM ('2021-07-21') TO ('2021-07-26');
CREATE TABLE account_changes_202107_06 PARTITION OF account_changes FOR VALUES FROM ('2021-07-26') TO ('2021-08-01');
CREATE TABLE account_changes_202108_01 PARTITION OF account_changes FOR VALUES FROM ('2021-08-01') TO ('2021-08-06');
CREATE TABLE account_changes_202108_02 PARTITION OF account_changes FOR VALUES FROM ('2021-08-06') TO ('2021-08-11');
CREATE TABLE account_changes_202108_03 PARTITION OF account_changes FOR VALUES FROM ('2021-08-11') TO ('2021-08-16');
CREATE TABLE account_changes_202108_04 PARTITION OF account_changes FOR VALUES FROM ('2021-08-16') TO ('2021-08-21');
CREATE TABLE account_changes_202108_05 PARTITION OF account_changes FOR VALUES FROM ('2021-08-21') TO ('2021-08-26');
CREATE TABLE account_changes_202108_06 PARTITION OF account_changes FOR VALUES FROM ('2021-08-26') TO ('2021-09-01');
CREATE TABLE account_changes_202109_01 PARTITION OF account_changes FOR VALUES FROM ('2021-09-01') TO ('2021-09-06');
CREATE TABLE account_changes_202109_02 PARTITION OF account_changes FOR VALUES FROM ('2021-09-06') TO ('2021-09-11');
CREATE TABLE account_changes_202109_03 PARTITION OF account_changes FOR VALUES FROM ('2021-09-11') TO ('2021-09-16');
CREATE TABLE account_changes_202109_04 PARTITION OF account_changes FOR VALUES FROM ('2021-09-16') TO ('2021-09-21');
CREATE TABLE account_changes_202109_05 PARTITION OF account_changes FOR VALUES FROM ('2021-09-21') TO ('2021-09-26');
CREATE TABLE account_changes_202109_06 PARTITION OF account_changes FOR VALUES FROM ('2021-09-26') TO ('2021-10-01');
CREATE TABLE account_changes_202110_01 PARTITION OF account_changes FOR VALUES FROM ('2021-10-01') TO ('2021-10-06');
CREATE TABLE account_changes_202110_02 PARTITION OF account_changes FOR VALUES FROM ('2021-10-06') TO ('2021-10-11');
CREATE TABLE account_changes_202110_03 PARTITION OF account_changes FOR VALUES FROM ('2021-10-11') TO ('2021-10-16');
CREATE TABLE account_changes_202110_04 PARTITION OF account_changes FOR VALUES FROM ('2021-10-16') TO ('2021-10-21');
CREATE TABLE account_changes_202110_05 PARTITION OF account_changes FOR VALUES FROM ('2021-10-21') TO ('2021-10-26');
CREATE TABLE account_changes_202110_06 PARTITION OF account_changes FOR VALUES FROM ('2021-10-26') TO ('2021-11-01');
CREATE TABLE account_changes_202111_01 PARTITION OF account_changes FOR VALUES FROM ('2021-11-01') TO ('2021-11-06');
CREATE TABLE account_changes_202111_02 PARTITION OF account_changes FOR VALUES FROM ('2021-11-06') TO ('2021-11-11');
CREATE TABLE account_changes_202111_03 PARTITION OF account_changes FOR VALUES FROM ('2021-11-11') TO ('2021-11-16');
CREATE TABLE account_changes_202111_04 PARTITION OF account_changes FOR VALUES FROM ('2021-11-16') TO ('2021-11-21');
CREATE TABLE account_changes_202111_05 PARTITION OF account_changes FOR VALUES FROM ('2021-11-21') TO ('2021-11-26');
CREATE TABLE account_changes_202111_06 PARTITION OF account_changes FOR VALUES FROM ('2021-11-26') TO ('2021-12-01');
CREATE TABLE account_changes_202112_01 PARTITION OF account_changes FOR VALUES FROM ('2021-12-01') TO ('2021-12-06');
CREATE TABLE account_changes_202112_02 PARTITION OF account_changes FOR VALUES FROM ('2021-12-06') TO ('2021-12-11');
CREATE TABLE account_changes_202112_03 PARTITION OF account_changes FOR VALUES FROM ('2021-12-11') TO ('2021-12-16');
CREATE TABLE account_changes_202112_04 PARTITION OF account_changes FOR VALUES FROM ('2021-12-16') TO ('2021-12-21');
CREATE TABLE account_changes_202112_05 PARTITION OF account_changes FOR VALUES FROM ('2021-12-21') TO ('2021-12-26');
CREATE TABLE account_changes_202112_06 PARTITION OF account_changes FOR VALUES FROM ('2021-12-26') TO ('2022-01-01');

-- 触发器
-- 保存账变
CREATE OR REPLACE FUNCTION account_change_save(p_bill_no VARCHAR, p_user_id INTEGER, p_type INTEGER, p_io INTEGER, p_amount DECIMAL)
    RETURNS VOID
AS $$
    DECLARE
        v_date DATE;
        v_created TIMESTAMPTZ;
        v_r user_accounts%ROWTYPE;
        v_frozen DECIMAL(16, 4);
        v_remain DECIMAL(16, 4);
        v_total DECIMAL(16, 4);
    BEGIN
        v_date = CURRENT_DATE;
        v_created = NOW();

        SELECT * INTO v_r FROM user_accounts WHERE user_id = p_user_id; -- 先列出当前系统余额
        IF v_r IS NULL THEN
            RETURN;
        END IF;

        IF p_io = 1 THEN                        -- 入账
            v_frozen = v_r.frozen;              -- 冻结不变
            v_remain = v_r.remain + p_amount;   -- 余额增加
            v_total = v_r.total + p_amount;     -- 总额增加
        ELSIF p_io = 2 THEN                    -- 出账
            v_frozen = v_r.frozen - p_amount;   -- 冻结减少
            v_remain = v_r.v_remain;            -- 余额不变
            v_total = v_r.total - p_amount;     -- 总额减少
        ELSIF p_io = 31 THEN                   -- 平账: 冻结
            v_frozen = v_r.frozen + p_amount;   -- 冻结增加
            v_remain = v_r.remain - p_amount;   -- 可用减少
            v_total = v_r.total;                -- 总额不变
        ELSIF p_io = 32 THEN                   -- 平账: 回退
            v_frozen = v_r.frozen - p_amount;   -- 冻结减少
            v_remain = v_r.remain + p_amount;   -- 余额增加
            v_total = v_r.total;                -- 总额不变
        END IF;

        -- 写入账变记录
        INSERT INTO account_changes
            (bill_no, date, user_id, user_name, amount, type, in_out, before_remain, before_frozen, before_total, after_remain, after_frozen, after_total, created)
        VALUES
            (p_bill_no, v_date, p_user_id, v_r.user_name, p_amount, p_type, p_io, v_r.remain, v_r.frozen, v_r.total, v_remain, v_frozen, v_total, v_created);
    END;
$$ LANGUAGE plpgsql;
